#! /usr/bin/env python
# -*- coding: utf-8 -*-

import cx_Oracle
import time
import char
import error

class sql:
	#下面分别是用户名，密码，数据库所在地址，数据库端口，数据库ID
	username='zhcong'
	passwd='123456'
	ip='1.1.1.2'
	port='1521'
	DBid='XE'

	#这是用于数据库操作的对象，详见cx_Orcale文档
	conn=None
	sqlexec=None
	flag=False
	TABLENAME=''
	timeid=None

	def __init__(self,timeid,username='',passwd='',ip='',port='',DBid=''):
		#先将用户名密码存储在代码中
		try:
			if(not username==''):
				self.username=username;self.passwd=passwd
				self.ip=ip;self.port=port;self.DBid=DBid
			dns=cx_Oracle.makedsn(self.ip,self.port,self.DBid)
			self.conn=cx_Oracle.connect(self.username,self.passwd,dns)
			if(self.conn==None):
				self.flag=False
				self.timeid=timeid
				return None
			#获得orcale光标
			self.sqlexec=self.conn.cursor()
			self.flag=True
			error.show('database connected.')
		except cx_Oracle.DatabaseError:
			self.flag=False
			error.show('database connect error.')
			raise Exception
			return None

	def __call__(self):
		#对象结束时关闭链接
		conn.close()

	#执行INSERT的SQL语句
	def InsertSql(self,string,params):
		try:
			self.sqlexec.execute(string,params)
			self.conn.commit()
			return True
		except cx_Oracle.DatabaseError:
			error.show('sql into execute error.')
			return False

	#执行SELECT的SQL语句
	def SelectSql(self,string):
		try:
			return self.sqlexec.execute(string).fetchall()
		except:
			error.show('sql select execute error.')
			return None

	#执行CREATE TABLE的SQL语句
	def CreateSql(self,string):
		try:
			self.sqlexec.execute(string)
			self.conn.commit()
			return True
		except:
			error.show('sql create execute error.')
			return False

	#插入数据
	def Insert(self,data):
		SQLstr='insert into '+self.TABLENAME+' \
		values(:id, :url,:time,:version,:expend,:path,:fatherurl,:host)'
		return self.InsertSql(SQLstr,data)

	#建表
	def Create(self):
		#表名使用时间的hash值，md5值太长不能作表名
		timestr='%s'%abs(hash('%s'%self.timeid))
		SQLstr='create table pageinfo'+timestr+'(id char(32) primary key,url char(512),time date,version char(4),\
		expend char(16),path char(256),fatherurl char(256),host char(256))'
		self.TABLENAME='pageinfo'+timestr
		error.show('create table '+self.TABLENAME+'.')
		return self.CreateSql(SQLstr)
	#选择语句，选择一个所有元祖所有属性
	def Select(self,TABLENAME,host):
		SQLstr='Select * from '+TABLENAME+' where host=\''+host+'\''
		result=self.SelectSql(SQLstr)
		pagelist=[]
		if(result==None):
			return None
		for row in result:
			pagelist.append({
			'id':row[0].strip(),
			'url':char.ChkAccord(row[1].strip()),
			'time':row[2],
			'version':row[3].strip(),
			'expend':row[4].strip(),
			'path':row[5].strip(),
			'fatherurl':row[6].strip(),
			'host':row[7].strip()
		})
		return pagelist

	#找到表中所有host
	def SelectHost(self,TABLENAME):
		hostlist=[]
		SQLstr='Select host from '+TABLENAME+' group by host'
		result=self.SelectSql(SQLstr)
		if(result==None):
			return None
		for row in result:
			hostlist.append(row[0].strip())
		return hostlist